package com.css.zfzx.fwsjcj.modules.analysis.repository.impl;

import com.css.bpm.platform.api.local.PlatformAPI;
import com.css.bpm.platform.org.role.repository.entity.RoleEntity;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.zfzx.fwsjcj.commen.util.ServerUtil;
import com.css.zfzx.fwsjcj.modules.analysis.repository.AnalysisMapNativeRepository;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * @Description: fwsjcj
 * @Author: lhl
 * @CreateDate: 2021/12/2 18:38
 */
@Repository
public class AnalysisMapNativeRepositoryImpl implements AnalysisMapNativeRepository {
    @PersistenceContext
    EntityManager entityManager;

    @Override
    public List<Object[]> queryAnalysisMap() {
        StringBuilder sql = new StringBuilder("select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '0' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_brick_structure WHERE del_flag='0'  and status>0 " +
                "group by belongto_area) bri on bri.structure_type=str.structure_type and bri.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='0' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select  str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '1' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_mul_concrete_structure  " +
                "WHERE del_flag='0' and status>0 group by belongto_area) mul on mul.structure_type=str.structure_type and mul.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='1' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str  " +
                "left join (select '2' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_high_structure WHERE del_flag='0' and status>0 " +
                "group by belongto_area) hig on hig.structure_type=str.structure_type and hig.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='2' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '3' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_factory_structure WHERE del_flag='0' and status>0 " +
                "group by belongto_area) fac on fac.structure_type=str.structure_type and fac.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='3' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '4' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_wide_structure WHERE del_flag='0' and status>0 " +
                "group by belongto_area) wid on wid.structure_type=str.structure_type and wid.belongto_area=str.belongto_area   " +
                "where str.del_flag='0' and str.structure_type='4' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '5' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_building_structure WHERE del_flag='0' and status>0 " +
                "group by belongto_area) bui on bui.structure_type=str.structure_type and bui.belongto_area=str.belongto_area   " +
                "where str.del_flag='0' and str.structure_type='5' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '6' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_wood_structure WHERE del_flag='0' and status>0 " +
                "group by belongto_area) woo on woo.structure_type=str.structure_type and woo.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='6' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '7' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_stone_structure WHERE del_flag='0'  and status>0 " +
                "group by belongto_area) sto on sto.structure_type=str.structure_type and sto.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='7' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area " +
                "union all " +
                "select str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area,count,sum " +
                "from fw_structure_form str " +
                "left join (select '8' as structure_type,belongto_area,count(*) count,sum(building_area) sum from fw_other_structure WHERE del_flag='0'  and status>0 " +
                "group by belongto_area) oth on oth.structure_type=str.structure_type and oth.belongto_area=str.belongto_area  " +
                "where str.del_flag='0' and str.structure_type='8' " +
                "group by str.structure_type,str.belongto_province,str.belongto_city,str.belongto_area");
        Query nativeQuery = entityManager.createNativeQuery(sql.toString());
        List<Object[]> resultList = nativeQuery.getResultList();
        return resultList;
    }
}
